#-*-coding:utf-8-*-
#!/usr/bin/python 
#filename: postgres.py 


import psycopg2
import win32api
import win32con

output = None

def msg(s):
    if output:
        output(s.decode("utf8"))
    else: 
        print(s.decode("utf8"))

#连接postgres数据库
def db_conn():
    conn = psycopg2.connect(database = "postgres",user = "postgres",
                            password = "vpgis",host = "127.0.0.1",
                            port = "5432") 
    conn.set_isolation_level(psycopg2.extensions.ISOLATION_LEVEL_AUTOCOMMIT)
    msg('''连接数据成功！''')
    cur = conn.cursor()
    #创建数据库vpgis
    msg('--------------------')
    msg('开始创建数据库')
    cur.execute('''-- Database: vpgis
                    -- DROP DATABASE vpgis;
                      
                        CREATE DATABASE vpgis
                            WITH OWNER = postgres
                             ENCODING = 'utf-8'
                             TABLESPACE = pg_default
                             LC_COLLATE = 'Chinese (Simplified)_People''s Republic of China.936'
                             LC_CTYPE = 'Chinese (Simplified)_People''s Republic of China.936'
                             CONNECTION LIMIT = -1;''')
    msg('创建数据库成功！')
    conn.commit()
    cur.close()
    conn.close()
#连接vpgis数据库
    conn1 = psycopg2.connect(database = "vpgis",user = "postgres",
                              password = "vpgis",host = "127.0.0.1",
                              port = "5432")
    conn1.set_isolation_level(psycopg2.extensions.ISOLATION_LEVEL_AUTOCOMMIT)
    msg('---------------------')
    msg('''连接vpgis数据成功！''')
    cur1 = conn1.cursor()
    #新建扩展postgis
    msg('---------------------')
    msg('开始创建postgis扩展')
    cur1.execute('''-- Extension: postgis
 
                    -- DROP EXTENSION postgis;
                     
                    CREATE EXTENSION postgis
                    SCHEMA public
                    VERSION "2.1.3";''')
    msg('创建postgis扩展成功！')
    #新建layer_camera表
    msg('---------------------')
    msg('开始创建layer_camera表')
    cur1.execute(''' -- Table: layer_camera
 
                    -- DROP TABLE layer_camera;
                     
                    CREATE TABLE layer_camera
                    (
                      gid character varying(100) NOT NULL,
                      camera_id character varying(100),
                      description character varying(100),
                      pick_id character varying(100),
                      geom geometry(Point,4326),
                      iconurl character varying(100),
                      camera_kakou character varying(100), 
                      camera_type character varying(100), 
                      CONSTRAINT layer_camera_pkey PRIMARY KEY (gid)
                    )
                    WITH (
                      OIDS=FALSE
                    );
                    ALTER TABLE layer_camera
                      OWNER TO postgres;
                    COMMENT ON COLUMN layer_camera.camera_kakou IS '是否是卡口（0：未知；1，是；2，否）';
                    COMMENT ON COLUMN layer_camera.camera_type IS '类型（0=未知 1=枪机 2=球机）';
                     
                     
                    -- Index: camera_geom_gist
                     
                    -- DROP INDEX camera_geom_gist;
                     
                    CREATE INDEX camera_geom_gist
                      ON layer_camera
                      USING gist
                      (geom);
                      ''')
    msg('创建layer_camera表成功！')
    #新建layer_cases表
    msg('---------------------')
    msg('开始创建layer_cases表')
    cur1.execute(''' -- Table: layer_cases
                      
                    -- DROP TABLE layer_cases;
                      
                    CREATE TABLE layer_cases
                    (
                      gid character varying(100) NOT NULL,
                      cases_id character varying(100),
                      description character varying(100),
                      pick_id character varying(100), -- 案件图层ID(cases_casesId)
                      geom geometry(Point,4326),
                      cases_type character varying(100), -- 案件类型
                      iconurl character varying(100), -- 案件图标路径
                      cases_occurred_time timestamp(6) without time zone, -- 案发时间
                      CONSTRAINT layer_cases_pkey PRIMARY KEY (gid)
                    )
                    WITH (
                      OIDS=FALSE
                    );
                    ALTER TABLE layer_cases
                      OWNER TO postgres;
                    COMMENT ON COLUMN layer_cases.pick_id IS '案件图层ID(cases_casesId)';
                    COMMENT ON COLUMN layer_cases.cases_type IS '案件类型';
                    COMMENT ON COLUMN layer_cases.iconurl IS '案件图标路径';
                    COMMENT ON COLUMN layer_cases.cases_occurred_time IS '案发时间';
                      
                      
                    -- Index: cases_geom_gist
                      
                    -- DROP INDEX cases_geom_gist;
                      
                    CREATE INDEX cases_geom_gist
                      ON layer_cases
                      USING gist
                      (geom);''')
    msg('创建layer_cases表成功！')
    #新建layer_goal表
    msg('---------------------')
    msg('开始创建layer_goal表')
    cur1.execute(''' -- Table: layer_goal
                      
                    -- DROP TABLE layer_goal;
                      
                    CREATE TABLE layer_goal
                    (
                      gid character varying(100) NOT NULL,
                      goal_id character varying(100),
                      description character varying(100),
                      pick_id character varying(100), -- car_goalId;person_goalId
                      geom geometry(Point,4326),
                      goal_type character varying(10), -- 目标类型：目标人0，目标车1
                      iconurl character varying(100),
                      goal_bj_time timestamp(6) without time zone, -- 目标出现时间(目标的北京时间)
                      CONSTRAINT layer_goal_pkey PRIMARY KEY (gid)
                    )
                    WITH (
                      OIDS=FALSE
                    );
                    ALTER TABLE layer_goal
                      OWNER TO postgres;
                    COMMENT ON COLUMN layer_goal.pick_id IS 'car_goalId;person_goalId';
                    COMMENT ON COLUMN layer_goal.goal_type IS '目标类型：目标人0，目标车1';
                    COMMENT ON COLUMN layer_goal.goal_bj_time IS '目标出现时间(目标的北京时间)';

                      
                    -- Index: goal_geom_gist
                      
                    -- DROP INDEX goal_geom_gist;
                      
                    CREATE INDEX goal_geom_gist
                      ON layer_goal
                      USING gist
                      (geom);  ''')
    msg('创建layer_goal表成功！')
    #新建视图
    msg('---------------------')
    msg('开始创建视图')
    for i in range(1, 10):
        cur1.execute('''-- View: camera%s_view

                    -- DROP VIEW camera%s_view;

                    CREATE OR REPLACE VIEW camera%s_view AS
                     SELECT layer_camera.gid,
                        layer_camera.camera_id,
                        layer_camera.description,
                        layer_camera.pick_id,
                        layer_camera.geom,
                        layer_camera.iconurl,
                        layer_camera.camera_kakou,
                        layer_camera.camera_type
                       FROM layer_camera
                      WHERE layer_camera.camera_type::text = '%s'::text;

                    ALTER TABLE camera%s_view
                      OWNER TO postgres;
                ''', (i,i,i,i,i) )
    msg('视图创建成功！')
    conn1.commit()
    cur1.close()
    conn1.close()
def conf():
    #打开注册列表
    key = win32api.RegOpenKey(win32con.HKEY_LOCAL_MACHINE,'SOFTWARE\PostgreSQL\Installations\postgresql-x64-9.3',
                         0,win32con.KEY_ALL_ACCESS|win32con.KEY_WOW64_64KEY )
    value = win32api.RegQueryValueEx(key, "Data Directory")[0]
    #关闭注册列表
    win32api.RegCloseKey(key)
    #获取postgres服务器配置文件目录
    pg_hba = value + '\pg_hba.conf'
    #打开pg_hba.conf配置文件
    title_txt = open(pg_hba,'r+') 
    IP_a = '127.0.0.1/32'
    IP_b = '0.0.0.0/0'
    msg ('---------------------')
    try:  
        
        full_txt = title_txt.readlines()
        msg('修改服务器配置文件')
        full_txt[79] = full_txt[79].replace(IP_a,IP_b)
        title_txt.close()
        title_txt = open(pg_hba, 'w')
        title_txt.write("".join(full_txt))
        msg('服务器配置文件修改完成') 
    finally:  
        title_txt.close()  
def db_delete():
    conn = psycopg2.connect(database = "postgres",user = "postgres",
                            password = "vpgis",host = "127.0.0.1",
                            port = "5432") 
    conn.set_isolation_level(psycopg2.extensions.ISOLATION_LEVEL_AUTOCOMMIT) 
    #连接vpgis数据库
    cur = conn.cursor()
    #删除vpgis数据库  
    cur.execute('DROP DATABASE vpgis')
    conn.commit()
    cur.close()
    conn.close()
def install():
    try:
        post_information = 0
        db_conn()
        conf()
    except psycopg2.OperationalError,e:
        msg('连接数据库失败！\n')
        post_information = 1
    except IOError,e:
        msg('没有找到配置文件！')
        post_information = 2
    except psycopg2.ProgrammingError,e:
        msg('数据库 "vpgis" 已经存在！')
        post_information = 3
    else:
        msg('---------------------')
        msg("数据库安装完毕！")
    return post_information
def uninstall(): 
    try:
        post_information = 0
        db_delete()
    except psycopg2.ProgrammingError:
        msg('数据库 "vpgis" 不存在')
        post_information = 1
    except psycopg2.OperationalError:
        msg('无法删除数据库，当前数据库正在被使用')
        post_information = 2
    else:
        msg('数据库删除成功！')  

    return post_information
if __name__ == '__main__':
    install()
    uninstall()